package vn.edu.saigontech.ncms.web.system;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import vn.edu.saigontech.ncms.model.info.AbstractReceipt;
import vn.edu.saigontech.ncms.model.info.Offering;
import vn.edu.saigontech.ncms.model.info.OfferingMember;
import vn.edu.saigontech.ncms.model.info.Student;
import vn.edu.saigontech.ncms.model.result.Grade;
import vn.edu.saigontech.ncms.service.OfferingMemberService;
import vn.edu.saigontech.ncms.service.OfferingService;
import vn.edu.saigontech.ncms.service.StudentService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;

@SuppressWarnings("serial")
public class OperatingStatusReportAction extends BaseAction {
	
	private int month;
	private int preMonth;
	private int preYear;
	private int year;
	private Long[] programsLabes = new Long[] { 9L, 17L, 12L, 13L, 14L, 6L };
	Collection<Offering> offerings;
	Collection<Offering> preOfferings;
	Collection<OfferingMember> absenceMembers;
	
	private static final int REPORTALL = 0;
	private static final int REPORTODD = 1;
	private static final int REPORTEVEN = 2;
	
	public String showReport() {
		return SUCCESS;
	}
	
	public String exportExcel() {
		HSSFWorkbook wb = ExcelUtil.getTemplate("operating-status-report.xls");
		if (Checker.isNull(wb)) return NONE;
			
		preMonth = DateUtil.getMonth(DateUtil.newDate(1, month-1, year)) + 1;
		preYear = DateUtil.getYear(DateUtil.newDate(1, month-1, year));
		offerings = OfferingService.INSTANCE.findAllOpeningByMonthYear(month, year);
		preOfferings = OfferingService.INSTANCE.findAllOpeningByMonthYear(preMonth, preYear);
		absenceMembers = OfferingMemberService.INSTANCE.findAbsenceByMonthYear(month, year, true);

		/* BEGIN WRITING DATA */
		HSSFSheet sheet = wb.getSheetAt(0);
		
		/* heading top */
		HSSFRow row = sheet.getRow(2);
		row.getCell(0).setCellValue("Tháng " + (month < 10 ? "0" + month : month) + " - " + year);
		
		int finalTestStartRow = 5;
		int absenceSummaryStartRow = 15;
		int studentSummaryStartRow = 25;
		int footerStartRow = 47;
		
		generateFinalTestSummary(finalTestStartRow, sheet);
		generateTestAbsenceSummary(absenceSummaryStartRow, sheet);
		generateStudentSummary(studentSummaryStartRow, sheet);
		generateFooter(footerStartRow, sheet);
		
		/* sheet 2 */
		HSSFSheet sheet1 = wb.getSheetAt(1);
		int startRow = 3;
		genereateListOfInactiveStudent(startRow, sheet1);
		/* end sheet 2 */
		
		/* sheet 2 */
		HSSFSheet sheet2 = wb.getSheetAt(2);
		genereateListOfAbsenceStudent(startRow, sheet2);
		/* end sheet 2 */
		
		/* sheet 2 */
		HSSFSheet sheet3 = wb.getSheetAt(3);
		genereateListOfFreeStudent(startRow, sheet3);
		/* end sheet 2 */
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Operating Status Report " + 
				month + "-" + year + ".xls\"");
		
		try {
			wb.write(RESPONSE().getOutputStream());
			this.addLoggingDetail(BaseAction.REPORT, "logging.system.operatingstatus.report.monthly", month, year);
		} catch (IOException ex) {
			ex.printStackTrace();
			return NONE;
		}
		return NONE;
	}
	
	/**
	 * generate the list of inactive students   
	 * */
	private void genereateListOfInactiveStudent(int startRow, HSSFSheet sheet) {
		Collection<OfferingMember> students = new ArrayList<OfferingMember>();
		for (Offering each : offerings)
			students.addAll(each.getInactiveMembers());
		int cRow = startRow;
		int count = 1;
		
		for (OfferingMember each : students) {			
			HSSFRow row = sheet.createRow(cRow++);
			row.createCell(0).setCellValue(count++);
			row.createCell(1).setCellValue(each.getReceipt().getStudent().getLastName());
			row.createCell(2).setCellValue(each.getReceipt().getStudent().getFirstName());
			row.createCell(3).setCellValue(each.getReceipt().getStudent().getGender()+"");
			row.createCell(4).setCellValue(each.getReceipt().getStudent().getDateOfBirth());
			row.createCell(5).setCellValue(each.getReceipt().getNumber());
			row.createCell(6).setCellValue(each.getReceipt().getOffering().getLevel().getProgram().getName());
			row.createCell(7).setCellValue(each.getReceipt().getOffering().getName());
			row.createCell(8).setCellValue(each.getComment());
		}
	}

	/**
	 * generate the list of final test absences students   
	 * */
	private void genereateListOfAbsenceStudent(int startRow, HSSFSheet sheet) {
		int cRow = startRow;
		int count = 1;
		
		for (OfferingMember each : absenceMembers) {			
			HSSFRow row = sheet.createRow(cRow++);
			row.createCell(0).setCellValue(count++);
			row.createCell(1).setCellValue(each.getReceipt().getStudent().getLastName());
			row.createCell(2).setCellValue(each.getReceipt().getStudent().getFirstName());
			row.createCell(3).setCellValue(each.getReceipt().getStudent().getGender()+"");
			row.createCell(4).setCellValue(each.getReceipt().getStudent().getDateOfBirth());
			row.createCell(5).setCellValue(each.getReceipt().getNumber());
			row.createCell(6).setCellValue(each.getReceipt().getOffering().getName());
			row.createCell(7).setCellValue(each.getReceipt().isActive()?"":"x");
			row.createCell(8).setCellValue(each.getComment());
		}
	}

	/**
	 * generate the list of free students   
	 * */
	private void genereateListOfFreeStudent(int startRow, HSSFSheet sheet) {
		Collection<OfferingMember> students = new ArrayList<OfferingMember>();
		for (Offering each : offerings)
			students.addAll(each.getListFreeStudents());
		int cRow = startRow;
		int count = 1;
		
		for (OfferingMember each : students) {			
			HSSFRow row = sheet.createRow(cRow++);
			row.createCell(0).setCellValue(count++);
			row.createCell(1).setCellValue(each.getReceipt().getStudent().getLastName());
			row.createCell(2).setCellValue(each.getReceipt().getStudent().getFirstName());
			row.createCell(3).setCellValue(each.getReceipt().getStudent().getGender()+"");
			row.createCell(4).setCellValue(each.getReceipt().getStudent().getDateOfBirth());
			row.createCell(5).setCellValue(each.getReceipt().getNumber());
			row.createCell(6).setCellValue(each.getReceipt().getOffering().getName());
			row.createCell(7).setCellValue(each.getReceipt().isActive()?"":"x");
			row.createCell(8).setCellValue(each.getComment());
		}
	}

	/**
	 *  generate data for final test summary 
	 */
	private void generateFinalTestSummary(int cRow, HSSFSheet sheet) {
		Collection<OfferingMember> offeringMembers = 
			OfferingMemberService.INSTANCE.findByEndDate(DateUtil.getStartDateOfMonth(month, year), 
					DateUtil.getEndDateOfMonth(month, year));
		Collection<OfferingMember> previousOfferingMembers = 
			OfferingMemberService.INSTANCE.findByEndDate(DateUtil.getStartDateOfMonth(month-1, year), 
					DateUtil.getEndDateOfMonth(month-1, year));
		
		int[] nGrades = countGrades(offeringMembers);
		int[] nPreGrades = countGrades(previousOfferingMembers);
		
		HSSFRow row = sheet.getRow(cRow++);
		HSSFCell cell = row.getCell(7);
		cell.setCellValue(cell.getStringCellValue() + (preMonth < 10 ? "0" + preMonth : preMonth));
		
		for (int i = 0; i < nGrades.length; i++) {
			row = sheet.getRow(cRow++);
			row.getCell(3).setCellValue(nGrades[i]);
			row.getCell(5).setCellFormula(row.getCell(5).getCellFormula());
			row.getCell(7).setCellValue(nPreGrades[i]);
		}
		row = sheet.getRow(cRow);
		row.getCell(3).setCellFormula(row.getCell(3).getCellFormula());
		row.getCell(5).setCellFormula(row.getCell(5).getCellFormula());
		row.getCell(7).setCellFormula(row.getCell(7).getCellFormula());
	}

	/**
	 *  generate data for final test summary 
	 */
	private void generateTestAbsenceSummary(int cRow, HSSFSheet sheet) {
		HSSFRow row = sheet.getRow(cRow++);
		HSSFCell cell = row.getCell(7);
		cell.setCellValue(cell.getStringCellValue() + (preMonth < 10 ? "0" + preMonth : preMonth));
		Collection<OfferingMember> preMembers = OfferingMemberService.INSTANCE.findAbsenceByMonthYear(preMonth, preYear, true);

		for (int i = 0; i < programsLabes.length; i++) {
			row = sheet.getRow(cRow++);
			row.getCell(2).setCellValue(countAbsences(programsLabes[i], absenceMembers));
			row.getCell(5).setCellFormula(row.getCell(5).getCellFormula());
			row.getCell(7).setCellValue(countAbsences(programsLabes[i], preMembers));
		}
		row = sheet.getRow(cRow);
		row.getCell(2).setCellFormula(row.getCell(2).getCellFormula());
		row.getCell(5).setCellFormula(row.getCell(5).getCellFormula());
		row.getCell(7).setCellFormula(row.getCell(7).getCellFormula());
	}

	/**
	 *  generate data for final test summary 
	 */
	private void generateStudentSummary(int cRow, HSSFSheet sheet) {
		HSSFRow row = sheet.getRow(cRow++);
		HSSFCell cell = row.getCell(7);
		cell.setCellValue(cell.getStringCellValue() + (preMonth < 10 ? "0" + preMonth : preMonth));
		int freeStudents = countFreeStudents(offerings);
		
		Date dateFrom = DateUtil.getStartDateOfMonth(month, year);
		Date dateTo = DateUtil.getEndDateOfMonth(month, year);
		Map<Student, List<AbstractReceipt>> studyList = StudentService.INSTANCE.findStudy(dateFrom, dateTo, true);
		
		Date preDateFrom = DateUtil.getStartDateOfMonth(preMonth, preYear);
		Date preDateTo = DateUtil.getEndDateOfMonth(preMonth, preYear);
		Map<Student, List<AbstractReceipt>> preStudyList = StudentService.INSTANCE.findStudy(preDateFrom, preDateTo, true);

		int totalStudents = 0, countOdd = 0, countEven = 0;
		for (int i = 0; i < programsLabes.length; i++) {
			row = sheet.getRow(cRow++);
			countOdd = countStudents(programsLabes[i], studyList, REPORTODD);
			countEven = countStudents(programsLabes[i], studyList, REPORTEVEN);
			row.getCell(2).setCellValue(countOffering(programsLabes[i], offerings));
			row.getCell(4).setCellValue(countEven);
			row.getCell(5).setCellValue(countOdd);
			row.getCell(3).setCellFormula(row.getCell(3).getCellFormula());
			row.getCell(6).setCellFormula(row.getCell(6).getCellFormula());
			row.getCell(7).setCellValue(countStudents(programsLabes[i], preStudyList, REPORTALL));
			
			totalStudents += (countOdd + countEven);
		}
		row = sheet.getRow(cRow++);
		row.getCell(2).setCellFormula(row.getCell(2).getCellFormula());
		row.getCell(3).setCellFormula(row.getCell(3).getCellFormula());
		row.getCell(4).setCellFormula(row.getCell(4).getCellFormula());
		row.getCell(5).setCellFormula(row.getCell(5).getCellFormula());
		row.getCell(6).setCellFormula(row.getCell(6).getCellFormula());
		row.getCell(7).setCellFormula(row.getCell(7).getCellFormula());
		
		row = sheet.getRow(cRow);
		row.getCell(0).setCellValue("* Có " + freeStudents + 
				" học viên miễn phí trong tổng số " + totalStudents + " học viên");
	}

	/**
	 *  generate data for final test summary 
	 */
	private void generateFooter(int cRow, HSSFSheet sheet) {
		HSSFRow row = sheet.getRow(cRow);
		Date now = DateUtil.newDate();
		int day = DateUtil.getDayOfMonth(now);
		int month = DateUtil.getMonth(now) + 1;
		int year = DateUtil.getYear(now);
		row.getCell(4).setCellValue("SaigonTech, ngày " + day + " tháng " + month + " năm  " + year);
	}
	
	@SuppressWarnings("deprecation")
	private int[] countGrades(Collection<OfferingMember> offeringMembers) {
		int a = 0, b = 0, c = 0, d = 0, e = 0, f = 0;
		for (OfferingMember each : offeringMembers) {
			if (each.getGrade1() == null) continue;
			if (each.getGrade1().equals(Grade.A)) a++;
			else if (each.getGrade1().equals(Grade.B)) b++; 
			else if (each.getGrade1().equals(Grade.C)) c++;
			else if (each.getGrade1().equals(Grade.D)) d++;
			else if (each.getGrade1().equals(Grade.E)) e++;
			else f++;
		}		
		return new int[] {a, b, c, d, e, f};
	}
	
	private int countAbsences(Long id, Collection<OfferingMember> members) {
		int count = 0;		
		for (OfferingMember each : members) {
			if (each.getReceipt().getOffering().getLevel().getProgram().getId().equals(id))
				count += 1; 
		}
		return count;
	}
	
	private int countStudents(Long id, Map<Student, List<AbstractReceipt>> preStudyList, int mode) {
		int count = 0;
		Set<Student> keys = preStudyList.keySet();
		for (Student each : keys) {
			List<AbstractReceipt> receipts = preStudyList.get(each);
			AbstractReceipt receipt = null;
			if (receipts.size() > 0) {
				receipt = getLatestOffering(receipts);
			}
			else continue;
			
			Offering offering = receipt.getOffering();
			switch (mode) {
				case REPORTALL:
					if (offering.getLevel().getProgram().getId().equals(id))
						count += 1;
					break;
				case REPORTODD:
					if (offering.getLevel().getProgram().getId().equals(id) && offering.getTueTeacher() != null)
						count += 1;
					break;
				case REPORTEVEN:
					if (offering.getLevel().getProgram().getId().equals(id) && offering.getMonTeacher() != null)
						count += 1;
					break;
			}
			
		}
		return count;
	}
	
	private AbstractReceipt getLatestOffering(List<AbstractReceipt> receipts) {
		Collections.sort(receipts, new Comparator<AbstractReceipt>(){
			@Override
			public int compare(AbstractReceipt o1, AbstractReceipt o2) {
				return o1.getOffering().getStartDate().compareTo(o2.getOffering().getStartDate());
			}
		});	
		Collections.reverse(receipts);
		return receipts.get(0);
	}

	private int countOffering(Long id, Collection<Offering> offerings) {
		int count = 0;
		for (Offering each : offerings)
			if (each.getLevel().getProgram().getId().equals(id))
				count++;
		return count;
	}

	private int countFreeStudents(Collection<Offering> offerings) {
		int count = 0;
		for (Offering each : offerings)
			count += each.getFreeStudents();
		return count;
	}
	
	public int getMonth() {
		return month;
	}

	public void setMonth(int month) {
		this.month = month;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}
}